USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetMyActiveAccounts')
	BEGIN
		DROP  Procedure  dbo.GetMyActiveAccounts
	END

GO

CREATE Procedure dbo.GetMyActiveAccounts
	@userId INT
AS
BEGIN
	
	;WITH A AS (
		-- ACCOUNT HEADER
		SELECT 
			act.PartnerName		AS AccountName
		,	act.PartnerNo		AS AccountNO
		,	act.PartnerGUID		AS PKID
		FROM TR_PARTNER as act WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r
			ON r.SourceGUID = act.PartnerGUID
		WHERE
			r.Partner2 = @userID
		AND r.RelationShipType = 3 -- OWNER
		AND act.PartnerCategory = 2 -- ORGANIZATION
		AND act.PartnerType = 2 -- CHILD ACCOUNT
		AND ISActive = 1
	), B AS (
		-- ADDRESS
		SELECT 
			c.CityName	AS [City]
		,	ad.State
		,	ad.Zip
		,	act.PKID
		FROM TR_ADDRESS as ad WITH (NOLOCK)
		INNER JOIN A as act
			ON act.PKID = ad.PartnerGUID
		LEFT OUTER JOIN TR_CITY as c WITH (NOLOCK)
			ON c.CityNo = ad.CityNo
	), Final AS (
		SELECT 
			A.AccountName		AS [Company Name]
		,	B.City				AS City
		,	B.State				AS [State]
		,	B.Zip				AS [Zip Code]
		,	A.PKID
		FROM A
		LEFT OUTER JOIN B
			on B.PKID = A.PKID
	)
	
	SELECT * FROM Final
END
GO


-- GetMyActiveAccounts 3